package comi.gsi.db;

import java.sql.SQLException;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DataBaseAndroid extends SQLiteOpenHelper{
	
	private static DataBaseAndroid singleton;
	public static final int DATABASE_VER = 1;
	public static final String DB_URL_WORKSPACE = "../GestioneSpese.sqlite";
	public static final String DB_URL_JAR = "./GestioneSpese.sqlite";
	public static String DB_URL = DB_URL_WORKSPACE;

	public static final DataBaseAndroid getSingleton(final Context context, final String name, final CursorFactory cursorFactory , final int version) {
		if (singleton == null) {
			synchronized (DataBaseAndroid.class) {
				if (singleton == null) {
					singleton = new DataBaseAndroid(context,name,cursorFactory,version);
				}
			} // if
		} // if
		return singleton;
	}
	
	public DataBaseAndroid(final Context context, final String name, final CursorFactory cursorFactory , final int version) {
		super(context, name, cursorFactory, version);
	}
	
	public DataBaseAndroid(final Context context, final String name, final int version) {
		super(context, name, null, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		try {
			generaDB(db);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method dbub
		
	}
	
	public static DataBaseAndroid generaApriDB(final Context context, final String posizioneDB) throws SQLException{
		
		 DataBaseAndroid dba = (DataBaseAndroid.getSingleton(context, posizioneDB,null, DATABASE_VER));
		 return dba;
	}
	
	public static SQLiteDatabase generaApriDB2(final Context context, final String posizioneDB) throws SQLException{
		SQLiteDatabase db = (new DataBaseAndroid(context, posizioneDB, DATABASE_VER)).getWritableDatabase();
		return db;
	}
	
	public static DataBaseAndroid apriDB(final Context context, final String posizioneDB) throws SQLException{
		
		 DataBaseAndroid dba = (DataBaseAndroid.getSingleton(context, posizioneDB,null,	DATABASE_VER));
		 SQLiteDatabase db = dba.getWritableDatabase();
		 return dba;
	}
	
	public static SQLiteDatabase apriDB2(final Context context, final String posizioneDB) throws SQLException{
		SQLiteDatabase db = (new DataBaseAndroid(context, posizioneDB, DATABASE_VER)).getWritableDatabase();
		return db;
	}
	
	
	public static void generaDB(final SQLiteDatabase db) throws SQLException {
		String sql = new String();
		sql = "CREATE TABLE \"Utenti\" (\"idUtente\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , \"nome\" TEXT NOT NULL , \"cognome\" TEXT NOT NULL , \"username\" TEXT NOT NULL  UNIQUE , \"password\" TEXT NOT NULL );";
		db.execSQL(sql.toString());
		sql = "CREATE TABLE \"gruppi\" (\"idGruppo\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , \"nome\" TEXT NOT NULL , \"descrizione\" TEXT);";
		db.execSQL(sql.toString());
		sql = "CREATE TABLE \"lookAndFeel\" (\"idLook\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , \"nome\" TEXT NOT NULL , \"valore\" TEXT NOT NULL , \"usato\" INTEGER NOT NULL );";
		db.execSQL(sql.toString());
		sql = "CREATE TABLE \"risparmio\" (\"idRisparmio\" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , \"PerSulTotale\" DOUBLE NOT NULL , \"nomeOggetto\" TEXT, \"codboOggetto\" DOUBLE);";
		db.execSQL(sql.toString());
		sql = "CREATE TABLE \"cat_spese\" (\"idCategoria\"  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\"descrizione\"  TEXT NOT NULL,\"importanza\"  TEXT NOT NULL,\"nome\"  TEXT NOT NULL,\"idGruppo\" INTEGER NOT NULL,CONdbRAINT \"keygruppo\" FOREIGN KEY (\"idGruppo\") REFERENCES \"gruppi\" (\"idGruppo\"));";
		db.execSQL(sql.toString());
		sql = "CREATE TABLE \"budget\" (\"idBudget\"  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\"idCategorie\"  INTEGER NOT NULL UNIQUE,\"percSulTot\"  DOUBLE NOT NULL,CONdbRAINT \"keyspesa\" FOREIGN KEY (\"idCategorie\") REFERENCES \"cat_spese\" (\"idCategoria\"));";
		db.execSQL(sql.toString());
		sql = "CREATE TABLE \"entrate\" (\"idEntrate\" INTEGER PRIMARY KEY  NOT NULL ,\"descrizione\" TEXT NOT NULL ,\"Fisse_o_Var\" TEXT NOT NULL ,\"inEuro\" INTEGER NOT NULL ,\"data\" TEXT NOT NULL ,\"nome\" TEXT NOT NULL ,\"idUtente\" INTEGER NOT NULL ,\"dataIns\" TEXT);";
		db.execSQL(sql.toString());
		sql = "CREATE TABLE \"single_spesa\" (\"idSpesa\" INTEGER PRIMARY KEY  NOT NULL ,\"Data\" TEXT NOT NULL ,\"inEuro\" INTEGER NOT NULL ,\"descrizione\" TEXT NOT NULL ,\"idCategorie\" INTEGER NOT NULL ,\"nome\" TEXT NOT NULL ,\"idUtente\" INTEGER NOT NULL ,\"dataIns\" TEXT);";
		db.execSQL(sql.toString());
		sql = "CREATE TABLE \"note\" (\"idNote\" INTEGER PRIMARY KEY  NOT NULL ,\"nome\" TEXT NOT NULL ,\"descrizione\" TEXT NOT NULL ,\"idUtente\" INTEGER NOT NULL ,\"data\" TEXT NOT NULL ,\"dataIns\" TEXT NOT NULL );";
		db.execSQL(sql.toString());
		db.close();
	}
}
